package com.weavernorth.retailExperienceAward;

import com.weavernorth.retailExperienceAward.utils.commonsUtil;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import org.apache.log4j.Logger;
import weaver.conn.ConnectionPool;
import weaver.conn.RecordSet;
import weaver.general.Util;
import weaver.interfaces.schedule.BaseCronJob;

import java.sql.Connection;
import java.sql.Statement;

import static com.weavernorth.retailExperienceAward.utils.commonsUtil.doubleFormat;
import static com.weavernorth.util.dateUtils.getHourMs;
import static com.weavernorth.util.dateUtils.getYearMonthDay;

/**
 * @Author: bruseewang
 * @Package: com.weavernorth.QyjlzdfjSummaryTimmer
 * @date: 2020/10/20 10:40
 * @description: 定时任务：区域经理分数与奖金计算
 */
public class QyjlzdfjSummaryTimmer extends BaseCronJob {
    public static ConnectionPool pool = ConnectionPool.getInstance();
    public Connection conn;
    public Statement statement;
    public String cycle;

    public String getcycle() {
        return cycle;
    }

    public void setcycle(String cycle) {
        this.cycle = cycle;
    }

    @Override
    public void execute() {
        Logger log = Logger4jUtil.getLoggerByName("/Timmer/QyjlzdfjSummaryTimmer/");
        log.info("区域经理分数计算接口执行开始----：");
        RecordSet rs = new RecordSet();
        RecordSet rs_update = new RecordSet();
        String strKhzq = dateUtils.previousMonth(0);
        if (cycle != null) {
            strKhzq = dateUtils.previousMonth(Integer.parseInt(cycle));
        }
        log.info("考核周期为：" + strKhzq);
        String nowDate = getYearMonthDay();//当前日期
        String strSql = "SELECT\n" +
                "\tb.id,\n" +
                "\tb.khzq,\n" +
                "\tb.xm,\n" +
                "\tb.gh,\n" +
                "\tb.fgs,\n" +
                "\tb.zw,\n" +
                "\tb.yybz35,\n" +
                "\tb.crm5,\n" +
                "\tb.fwbz40,\n" +
                "\tb.clbz20,\n" +
                "\tb.hr10,\n" +
                "\tb.zdf,\n" +
                "\tb.khrq,\n" +
                "\tc.lastname,\n" +
                "\tc.jobtitle,\n" +
                "\td.jobtitlename,\n" +
                "\td.ecology_pinyin_search AS zwpy,\n" +
                "\te.jjjs AS jjjs_gr, f.qyjljs AS jjjs_gs,\n" +
                "\tNVL( qj.qjts, 0 ) AS qjts \n" +
                "FROM\n" +
                "\tuf_zddpgrjj_qyjl b\n" +
                "\tINNER JOIN hrmresource c ON b.xm = c.id \n" +
                "\tINNER JOIN hrmjobtitles d ON b.zw = d.id \n" +
                "\tLEFT JOIN uf_jjjspzb_gr e ON b.xm = e.ygxm \n" +
                "\tLEFT JOIN uf_jjjspzb f ON b.fgs = f.fgs \n" +
                "\tLEFT JOIN w_all_leavelist qj ON SUBSTR( b.khzq, 1, 4 ) = qj.year \n" +
                "\tAND SUBSTR(b.khzq, 5, 2 ) = qj.month \n" +
                "\tAND b.xm = qj.resourceid \n" +
                "WHERE b.khzq='" + strKhzq + "' and (tsjg is null or tsjg=1)";
        rs.execute(strSql);
        String str_jjjs = null;
        String str_jjjs_gr = null;
        String str_jjjs_gs = null;
        Double db_jjje = 0.00;
        Double db_qjDays = 0.00;
        try {
            while (rs.next()) {
                //id
                String strId = Util.null2String(rs.getString("id"));
                //区域经理
                String strXm = Util.null2String(rs.getString("xm"));
                //获取服务标准评分
                Double db_Fwbz = Util.getDoubleValue(rs.getString("fwbz40"), 0.00);
                //获取陈列管理
                Double db_Clgl = Util.getDoubleValue(rs.getString("clbz20"), 0.00);
                //获取CRM评分
                Double db_CRM = Util.getDoubleValue(rs.getString("crm5"), 0.00);
                //获取零售运营评分
                String strYybzpf = getAvg_yybz(log, strKhzq, strXm);
                Double db_Lsyy = Util.getDoubleValue(strYybzpf, 0.00);
                //个人奖金基数
                str_jjjs_gr = Util.null2String(rs.getString("jjjs_gr"));
                //分公司奖金基数
                str_jjjs_gs = Util.null2String(rs.getString("jjjs_gs"));
                if (!"".equals(str_jjjs_gr) && str_jjjs_gr != null) {
                    str_jjjs = str_jjjs_gr;
                } else {
                    str_jjjs = str_jjjs_gs;
                }
                //region[添加hr评分 add by wangwenbin at 20220214]
                //获取CRM评分
                Double db_hr = Util.getDoubleValue(rs.getString("hr10"), 0.00);
                //endregion
                //总得分
                Double db_zdf = db_Fwbz + db_Clgl + db_CRM + db_Lsyy + db_hr;
                //请假天数
                db_qjDays = Util.getDoubleValue(rs.getString("qjts"), 0.00);
                log.info("请假天数：" + db_qjDays);
                log.info("总得分：" + db_zdf);
                log.info("奖金基数：" + str_jjjs);
                if (str_jjjs != null && !"".equals(str_jjjs) && db_zdf != 0.00) {
                    db_jjje = (db_zdf / 100) * Util.getDoubleValue(str_jjjs, 0.00);
                    log.info("奖金金额：" + db_jjje);
                    //根据请假天数调整奖金金额
                    db_jjje = doubleFormat(commonsUtil.convertAmount(db_jjje, db_qjDays));
                    log.info("根据请假天数调整奖金金额：" + db_jjje);
                }
                //不计算店铺状态为2(闭店)的总得分
                String strUpdateSql = "update uf_zddpgrjj_qyjl set zdf=" + db_zdf + ",yybz35='" + strYybzpf + "',hr10='" + db_hr + "',jjje='" + db_jjje + "',qjts='" + db_qjDays + "',MODEDATAMODIFIER='2',MODEDATAMODIFYDATETIME='" + nowDate + " " + getHourMs() + "'  where id='" + strId + "'";
                log.info("更新语句：" + strUpdateSql);
                rs_update.execute(strUpdateSql);
            }
            log.info("区域经理分数分计算接口执行结束----：");
        } catch (Exception ex) {
            log.info("区域经理分数分计算接口异常，异常信息：" + ex.toString());
        }
    }

    /***
     * 得到区域经理负责店铺的营运标准评分的平均分
     * @param strKhzq 考核周期
     * @param strxm 区域经理
     * @return
     */
    public String getAvg_yybz(Logger log, String strKhzq, String strxm) {
        RecordSet rs_getZfs_avg = new RecordSet();//获取周期内店铺总分数的平均分
        String strGetAvg = "";//平均分
        String strYybzfs = "";//营运标准评分
        //Integer count_All=0;//区域经理负责的所有店铺数量
        Integer counts = 0;//区域经理负责的已审批完的店铺数量
        String str_AvgCount = "SELECT a.khzq,a.jcr,count(a.dp) counts FROM uf_qyjlpf a where a.khzq='" + strKhzq + "' and NOT EXISTS(SELECT 1 FROM uf_yddpzxygxxd h WHERE  a.DP=h.dp AND h.khzq='" + strKhzq + "')  and a.jcr='" + strxm + "' GROUP BY a.khzq,a.jcr ";
        log.info("获取区域经理管辖下已审批完的店铺数量Sql:" + str_AvgCount);
        RecordSet rs_counts = new RecordSet();
        rs_counts.executeQuery(str_AvgCount);
        while (rs_counts.next()) {
            counts = Util.getIntValue(rs_counts.getString("counts"), 0);
        }
        log.info("区域经理管辖下已审批的店铺数量：" + counts);
        log.info("区域经理：" + strxm + "已审批玩的店铺数：" + counts);
        String str_getAvg = "SELECT khzq,jcr,sum(zfs) sums FROM uf_qyjlpf where khzq='" + strKhzq + "' and jcr='" + strxm + "' GROUP BY khzq,jcr";
        log.info("获取营运标准平均分sql:" + str_getAvg);

        rs_getZfs_avg.executeQuery(str_getAvg);
        while (rs_getZfs_avg.next()) {
            strGetAvg = Util.null2String(Math.round(Util.getDoubleValue(rs_getZfs_avg.getString("sums"), 0.00) / counts));
            log.info("营运标准平均分：" + strGetAvg);
        }
        if (!"".equals(strGetAvg)) {
            Double avg = Util.getDoubleValue(strGetAvg, 0.00);
            if (avg >= 80) {
                strYybzfs = "35.00";
            } else {
                strYybzfs = "0.00";
            }
        }
        return strYybzfs;
    }
}
